package utli;

import entity.Staffs;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBCon {
        // MySQL8.0以下版本 - JDBC驱动名及数据库URL
        static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
        // test为数据库名,?后面这句很重要,设置useSSl=false
        static final String DB_URL = "jdbc:mysql://localhost:3306/jdbc_test";

        // 数据库的用户名与密码，需要根据自己的设置
        static final String USER = "root";
        static final String PASS = "yxy2580";

        static {
            try {
                Class.forName(JDBC_DRIVER);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
        //链接数据库
        public static Connection getCon() {
            try {
                return DriverManager.getConnection(DB_URL,USER,PASS);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }

    public static void close(PreparedStatement stat,Connection conn) {
        try {
            if(stat!=null) stat.close();
            if(conn!=null) conn.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

        //关闭数据库
        public static void close(ResultSet rs,PreparedStatement stat,Connection conn) {
            try {
                if(rs!=null) rs.close();
                if(stat!=null) stat.close();
                if(conn!=null) conn.close();
            } catch (Exception e){
                e.printStackTrace();
            }
        }
        //增删改
        public static int ex(String sql) {
            Connection conn = null;
            PreparedStatement stat = null;
            int result = 0;
            try {
                conn = getCon();
                stat = conn.prepareStatement(sql);
                result = stat.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return result;
        }

        public static List<Staffs> query(String sql) {
            Connection conn = null;
            PreparedStatement stat = null;
            ResultSet rs = null;
            List<Staffs> list = new ArrayList<Staffs>();
            Staffs sta = null;
            try {
                conn = getCon();
                stat = conn.prepareStatement(sql);
                rs = stat.executeQuery();
                while(rs.next()) {
                    sta = new Staffs();
                    sta.setId(rs.getInt("id"));
                    sta.setName(rs.getString("name"));
                    sta.setAge(rs.getInt("age"));
                    sta.setPhone(rs.getString("phone"));
                    sta.setPos(rs.getString("pos"));
                    sta.setAdd_time(rs.getString("add_time"));
                    sta.setUpdate_time(rs.getString("update_time"));
                }
                list.add(sta);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return list;
        }

}
